package com.timeQueryServlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class BuildingTimeQuery extends HttpServlet {

	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String building=request.getParameter("building");
		int week=Integer.parseInt(request.getParameter("week"));
		int time=Integer.parseInt(request.getParameter("time"));
		String[] Time={"8:00-9:50","10:00-11:50","12:00-13:50","14:00-15:50","16:00-17:50","18:00-19:50"};
		String   selectTime=null;
		String sql=null;
		if(time>=1 && time <= 6)
		{
			int scheduleTime=week*10+time;
			sql="select *  from roomno r"
				+" where "
				+" NOT EXISTS "
				+"  ( select * from  schedule s" 
				+"  where r.roomNo=s.roomNo"
				+" and r.buildingNo=s.buildingNo " 
				+" and s.time=" +scheduleTime + " ) "
				+" and r.buildingNo='"+building+"'";
			selectTime=Time[time-1];
			
		}
		else if(time==12)
		{
		    int scheduleTime1=week*10+1;
		    int scheduleTime2=week*10+2;
		    
		    sql="select *  from roomno r"
				+" where "
				+" NOT EXISTS "
				+"  ( select * from  schedule s" 
				+"  where r.roomNo=s.roomNo"
				+" and r.buildingNo=s.buildingNo " 
				+" and s.time >=" +scheduleTime1 
				+" and time <=" +scheduleTime2 +" ) "
				+" and r.buildingNo='"+building+"'";
		    selectTime="8:00-11:50";
		}
		else if(time==45)
		{
		    int scheduleTime1=week*10+4;
		    int scheduleTime2=week*10+5;
		    sql="select * from roomno r where "
	   			  +"NOT EXISTS"
	   			  +" ( select * from schedule s"
	   			  +" where s.buildingNo=r.buildingNo"
	   			  +" and s.roomNo=r.roomNo "
	   			  +" and time >=" +scheduleTime1
	   			  +" and time <=" +scheduleTime2 +")"
	   		      +" and r.buildingNo='"+building+"'";
		    
			selectTime="14:00-17:50";
		}
		else
		{
			
		}
		request.getSession().setAttribute("sql", sql);
		request.getSession().setAttribute("time", selectTime);
		request.getSession().setAttribute("week", week);
		response.sendRedirect("buildingTime.jsp");
	}

}
